SQL Sub Queries

    In SQL if we write a query inside another query, then it would be considered as a nested, inner query, and subquery. Generally, these subqueries applied using the SQL WHERE clause.

    SQL Sub Queries

    Subqueries are basically used to set a condition expression, that’s why the subquery executes before the main query. The data sets returned by the subquery become the part of condition expression on which the main query act.

    Rules to perform a Subquery

    There are some set of rules which we need to follow if we want to perform the subquery.

    • The subquery must be associated with the main query using WHERE, HAVING and FROM clause.
    • The subquery must return a result so it should be used with SELECT statement.
    • The Mainquery could either be a SELECT, UPDATE, INSERT or DELETE statement.
    • The subquery must be inside the main query and enclosed in parentheses.
    • The subquery must be written on the right side of the comparison operator.
    • If the subquery supposed to return more than one value then use the multiple value operators (IN, BETWEEN) in the Mainquery .
    • If the subquery returns a single row, then use single value operators such as =, <=, >=, etc. in the main query.

    Subquery Syntax:

    Select Syntax 

    SELECT column_name,…
FROM table_name_1
WHERE column_name_x expression operator
(SELECT column_name_x  from table_name_2   WHERE ... );

    Insert Syntax 

    INSERT INTO Table_Name_1
(SELECT columns FROM Table_name_2);

    Update Syntax 

    UPDATE Table_name_1
SET column_name = value
WHERE column_name_n IN (SELECT column_name_n  FROM Table_Name_2 condition);

    Delete Syntax 

    DELETE FROM Table_name_1
WHERE column_name_n IN (SELECT column_name_n FROM Table_name_2 condition);

    Example

    For the examples consider these two sample tables. Students 

    +------+--------+------+--------+-------+----------+
| id   | name   | age  | grades | marks | Trade    |
+------+--------+------+--------+-------+----------+
|    1 | Luffy  |   16 | A      |   970 | Science  |
|    2 | Naruto |   18 | A      |   960 | Humanity |
|    3 | Zoro   |   20 | A      |   940 | Commerce |
|    4 | Sanji  |   21 | B      |   899 | Humanity |
|    5 | Nami   |   17 | B      |   896 | Science  |
|    6 | Robin  |   20 | B      |   860 | Humanity |
|    7 | Goku   |   27 | B      |   860 | Humanity |
+------+--------+------+--------+-------+----------+

    Library 

    +---------+-------------------+------------+
| Book_ID | Book_Name         | Student_ID |
+---------+-------------------+------------+
|    1124 | One Hundred years |          1 |
|    1104 | The Great Escape  |          2 |
|    1209 | Beloved           |          6 |
|    1111 | Hollow            |          4 |
|    2351 | Invisible Man     |          3 |
|    1034 | A Passage         |          2 |
|    1211 | Hero              |          6 |
|    1188 | Your Name         |          5 |
|    1211 | Hero              |          6 |
|    1000 | My Hope           |          8 |
|    1000 | Go Away           |         10 |
+---------+-------------------+------------+

    Query: Show the ID, names and trade of those students who have borrowed books from the Library. Main Query: Show the ID, Names and Trade of students. Sub Query: Return Students ID, who have borrowed books from the Library. 

    SELECT id, name, trade
FROM students
WHERE id IN (SELECT Student_ID FROM Library);

    Output 

    +------+--------+----------+
| id   | name   | trade    |
+------+--------+----------+
|    1 | Luffy  | Science  |
|    2 | Naruto | Humanity |
|    3 | Zoro   | Commerce |
|    4 | Sanji  | Humanity |
|    5 | Nami   | Science  |
|    6 | Robin  | Humanity |
+------+--------+----------+

    <Note>: The Above query can also be performed using a SQL JOIN statement while dealing with the database we often use JOIN statement instead of Subqueries because subqueries are slower than JOIN.

    Summary

    • A query inside another query is known as a Subquery.
    • A subquery is also known as a nested and inner query.
    • The Subquery act as a conditional expression for the main query.
    • The query which bound the subquery is known as the Main Query.
    • The subquery must be enclosed in parentheses.
    • The subquery can be performed within the SELECT, INSERT, UPDATE and DELETE statements of the main query.
    • The subquery must return a result.
    • Subquery gets executed before the main query.

    People are also reading:

    Previous Tutorial Next Tutorial